--    Program: 		CAPAREXT_TRANSACTION_SOURCE.sql
--    Description:  Extract AR Transaction Source information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(20);
   
   l_set_of_books_id		number;
   
   cursor cur_Transaction_Source (p_set_of_books_id number) is
      select rbsa.* 
      from   RA_BATCH_SOURCES_ALL rbsa,
             hr_organization_information hoi
	  where  hoi.org_information_context = 'Operating Unit Information'
	  and    hoi.org_information3 = to_char(p_set_of_books_id)
	  and    hoi.organization_id = rbsa.org_id;

begin
   l_pathname := '/tmp';
   l_filename := 'CAPAR_TRANSACTION_SOURCE.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
       --raise;
   end;      

   for cur in cur_Transaction_Source(l_set_of_books_id) loop   	          
     -- output data
     utl_file.put_line( l_utl_file, 
       CUR.BATCH_SOURCE_ID              ||chr(9)||
       CUR.LAST_UPDATE_DATE             ||chr(9)||
       CUR.LAST_UPDATED_BY              ||chr(9)||
       CUR.CREATION_DATE                ||chr(9)||
       CUR.CREATED_BY                   ||chr(9)||
       CUR.LAST_UPDATE_LOGIN            ||chr(9)||
       CUR.NAME                         ||chr(9)||
       CUR.ORG_ID                       ||chr(9)||
       CUR.DESCRIPTION                  ||chr(9)||
       CUR.STATUS                       ||chr(9)||
       CUR.LAST_BATCH_NUM               ||chr(9)||
       CUR.DEFAULT_INV_TRX_TYPE         ||chr(9)||
       CUR.ATTRIBUTE_CATEGORY           ||chr(9)||
       CUR.ATTRIBUTE1                   ||chr(9)||
       CUR.ATTRIBUTE2                   ||chr(9)||
       CUR.ATTRIBUTE3                   ||chr(9)||
       CUR.ATTRIBUTE4                   ||chr(9)||
       CUR.ATTRIBUTE5                   ||chr(9)||
       CUR.ATTRIBUTE6                   ||chr(9)||
       CUR.ATTRIBUTE7                   ||chr(9)||
       CUR.ATTRIBUTE8                   ||chr(9)||
       CUR.ATTRIBUTE9                   ||chr(9)||
       CUR.ATTRIBUTE10                  ||chr(9)||
       CUR.ACCOUNTING_FLEXFIELD_RULE    ||chr(9)||
       CUR.ACCOUNTING_RULE_RULE         ||chr(9)||
       CUR.AGREEMENT_RULE               ||chr(9)||
       CUR.AUTO_BATCH_NUMBERING_FLAG    ||chr(9)||
       CUR.AUTO_TRX_NUMBERING_FLAG      ||chr(9)||
       CUR.BATCH_SOURCE_TYPE            ||chr(9)||
       CUR.BILL_ADDRESS_RULE            ||chr(9)||
       CUR.BILL_CONTACT_RULE            ||chr(9)||
       CUR.BILL_CUSTOMER_RULE           ||chr(9)||
       CUR.CREATE_CLEARING_FLAG         ||chr(9)||
       CUR.CUST_TRX_TYPE_RULE           ||chr(9)||
       CUR.DERIVE_DATE_FLAG             ||chr(9)||
       CUR.END_DATE                     ||chr(9)||
       CUR.FOB_POINT_RULE               ||chr(9)||
       CUR.GL_DATE_PERIOD_RULE          ||chr(9)||
       CUR.INVALID_LINES_RULE           ||chr(9)||
       CUR.INVALID_TAX_RATE_RULE        ||chr(9)||
       CUR.INVENTORY_ITEM_RULE          ||chr(9)||
       CUR.INVOICING_RULE_RULE          ||chr(9)||
       CUR.MEMO_REASON_RULE             ||chr(9)||
       CUR.REV_ACC_ALLOCATION_RULE      ||chr(9)||
       CUR.SALESPERSON_RULE             ||chr(9)||
       CUR.SALES_CREDIT_RULE            ||chr(9)||
       CUR.SALES_CREDIT_TYPE_RULE       ||chr(9)||
       CUR.SALES_TERRITORY_RULE         ||chr(9)||
       CUR.SHIP_ADDRESS_RULE            ||chr(9)||
       CUR.SHIP_CONTACT_RULE            ||chr(9)||
       CUR.SHIP_CUSTOMER_RULE           ||chr(9)||
       CUR.SHIP_VIA_RULE                ||chr(9)||
       CUR.SOLD_CUSTOMER_RULE           ||chr(9)||
       CUR.START_DATE                   ||chr(9)||
       CUR.TERM_RULE                    ||chr(9)||
       CUR.UNIT_OF_MEASURE_RULE         ||chr(9)||
       CUR.ATTRIBUTE11                  ||chr(9)||
       CUR.ATTRIBUTE12                  ||chr(9)||
       CUR.ATTRIBUTE13                  ||chr(9)||
       CUR.ATTRIBUTE14                  ||chr(9)||
       CUR.ATTRIBUTE15                  ||chr(9)||
       CUR.CUSTOMER_BANK_ACCOUNT_RULE   ||chr(9)||
       CUR.MEMO_LINE_RULE               ||chr(9)||
       CUR.RECEIPT_METHOD_RULE          ||chr(9)||
       CUR.RELATED_DOCUMENT_RULE        ||chr(9)||
       CUR.ALLOW_SALES_CREDIT_FLAG      ||chr(9)||
       CUR.GROUPING_RULE_ID             ||chr(9)||
       CUR.CREDIT_MEMO_BATCH_SOURCE_ID  ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE1            ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE2            ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE3            ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE4            ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE5            ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE6            ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE7            ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE8            ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE9            ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE10           ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE11           ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE12           ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE13           ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE14           ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE15           ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE16           ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE17           ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE18           ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE19           ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE20           ||chr(9)||
       CUR.GLOBAL_ATTRIBUTE_CATEGORY    ||chr(9)||
       CUR.COPY_DOC_NUMBER_FLAG         ||chr(9)||
       CUR.DEFAULT_REFERENCE
	 );
     
	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Transaction Source not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPAREXT_TRANSACTION_SOURCE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
